/*
 *   Copyright (C) 2010  Jens Kieselbach
 * 
 *   This program is free software: you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation, either version 3 of the License, or
 *   (at your option) any later version.
 * 
 *   This program is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.
 * 
 *   You may obtain a copy of the License at http://www.gnu.org/licenses/gpl.html
 */

package de.kieselbach.grootional.component

import com.jgoodies.forms.builder.*
import com.jgoodies.forms.layout.*
import de.kieselbach.grootional.util.*
import de.kieselbach.grootional.value.*
import de.kieselbach.sql.*
import de.kieselbach.swing.*
import de.kieselbach.swing.border.*
import de.kieselbach.swing.table.*
import java.sql.*
import java.util.logging.*
import javax.swing.*
import javax.swing.tree.*

import java.awt.BorderLayout
import java.awt.Color
import java.awt.Dimension
import java.awt.Graphics
import java.awt.Point

class TableMetaDataCard extends Card {

    def uiResources
    def resourceBundle

    def connection

    def schemasAndCatalogs = [] as List
    def schemaAndCatalog

    def tableTypes = [] as List
    def tablesPerType = [:] as TreeMap
    def currentType
    def currentTable

    def refreshButton
    def linkedButton
    def selectButton

    def tableComboBox
    def typeComboBox

    def columnTable
    def columnModel
    def columnScroll

    def pkTable
    def pkModel

    def fkTable
    def fkModel

    def uniqueTable
    def uniqueModel

    def indexTable
    def indexModel

    def infoBrowser
    def infoBrowserSelectionChanged

    TableMetaDataCard(def uires, def con, def infobrowser) {

        super(new BorderLayout())
        processable = true
        addToolBarGlue = false

        infoBrowser = infobrowser
        uiResources = uires
        resourceBundle = uiResources.resourceBundles['gui']

        connection = con
        schemaAndCatalog = new CatalogSchemaValue(con.connectionValue.catalog, con.connectionValue.schema)

        title = resourceBundle.getString('tableMetadata.title')
        icon = uiResources.images['metadata16'] as Icon

        border = CustomLineBorder.create(UIUtilities.lineBorderColor, '8426')

        refreshButton = new FlatButton(uiResources.images['refresh16'])
        refreshButton.enabled = false
        refreshButton.toolTipText = resourceBundle.getString('tableMetadata.refresh')
        toolBarItems << refreshButton
        refreshButton.actionPerformed = {
            if(linkedButton.selected && infoBrowser.schemaAndCatalog) {
                schemaAndCatalog = infoBrowser.schemaAndCatalog
                refresh(infoBrowser.selectedType, infoBrowser.selectedTable, infoBrowser.selectedColumn)
            }
            else {
                refresh()
            }
        }

        toolBarItems << Box.createHorizontalGlue()
        
        linkedButton = new JToggleButton(uiResources.images['linked16'])
        linkedButton.enabled = false
        linkedButton.selected = true
        linkedButton.toolTipText = resourceBundle.getString('tableMetadata.linked')
        toolBarItems << linkedButton
        linkedButton.actionPerformed = {

        }

        selectButton = new FlatButton(uiResources.images['dropdown16'])
        selectButton.enabled = false
        selectButton.toolTipText = resourceBundle.getString('tableMetadata.catalogandschema')
        toolBarItems << selectButton
        selectButton.actionPerformed = {
            selectCatalohAndSchema()
        }

        toolBarItems << new CardSeparator()

        def gui = new JPanel(new BorderLayout(5, 5))
        gui.border = BorderFactory.createEmptyBorder(8, 8, 8, 8)
        add(gui, BorderLayout.CENTER)

        // -------------------------------------------------------

        tableComboBox = new JComboBox()
        typeComboBox = new JComboBox()

        columnTable = new JTable()
        columnModel = new CommonTableModel([resourceBundle.getString('tableMetadata.pk'),
                resourceBundle.getString('tableMetadata.fk'),
                resourceBundle.getString('tableMetadata.name'),
                resourceBundle.getString('tableMetadata.jdbcType'),
                resourceBundle.getString('tableMetadata.type'),
                resourceBundle.getString('tableMetadata.size'),
                resourceBundle.getString('tableMetadata.digits'),
                resourceBundle.getString('tableMetadata.notNull')] as ArrayList)
        columnModel.valueClass = {col ->
            switch(col) {
                case 0:
                case 1:
                case 7:
                return Boolean.class
                case 5:
                case 6:
                return Integer.class
                default:
                return String.class
            }
        }
        columnModel.valueEditable = {cols, row, col ->
            false
        }
        columnTable.model = columnModel

        // -------------------------------------------------------

        def tabpane = new JTabbedPane()

        pkTable = new JTable()
        pkModel = new CommonTableModel([resourceBundle.getString('tableMetadata.name'),
                resourceBundle.getString('tableMetadata.fields')] as ArrayList)
        pkModel.valueClass = {col ->
            String.class
        }
        pkModel.valueEditable = {cols, row, col ->
            false
        }
        pkTable.model = pkModel

        def t1 = new JPanel(new BorderLayout(0, 0))
        t1.add(new JScrollPane(pkTable), BorderLayout.CENTER)
        t1.border = BorderFactory.createEmptyBorder(4, 4, 4, 4)
        tabpane.add(resourceBundle.getString('tableMetadata.primaryKeys'), t1)

        fkTable = new JTable()
        fkModel = new CommonTableModel([resourceBundle.getString('tableMetadata.name'),
                resourceBundle.getString('tableMetadata.fields'),
                resourceBundle.getString('tableMetadata.foreignKeyTable'),
                resourceBundle.getString('tableMetadata.foreignKeyFields'),
                resourceBundle.getString('tableMetadata.updateRule'),
                resourceBundle.getString('tableMetadata.deleteRule')] as ArrayList)
        fkModel.valueClass = {col ->
            String.class
        }
        pkModel.valueEditable = {cols, row, col ->
            false
        }
        fkTable.model = fkModel

        def t2 = new JPanel(new BorderLayout(0, 0))
        t2.add(new JScrollPane(fkTable), BorderLayout.CENTER)
        t2.border = BorderFactory.createEmptyBorder(4, 4, 4, 4)
        tabpane.add(resourceBundle.getString('tableMetadata.foreignKeys'), t2)

        uniqueTable = new JTable()
        uniqueModel = new CommonTableModel([resourceBundle.getString('tableMetadata.name'),
                resourceBundle.getString('tableMetadata.fields')] as ArrayList)
        uniqueModel.valueClass = {col ->
            String.class
        }
        uniqueModel.valueEditable = {cols, row, col ->
            false
        }
        uniqueTable.model = uniqueModel

        def t3 = new JPanel(new BorderLayout(0, 0))
        t3.add(new JScrollPane(uniqueTable), BorderLayout.CENTER)
        t3.border = BorderFactory.createEmptyBorder(4, 4, 4, 4)
        tabpane.add(resourceBundle.getString('tableMetadata.uniqueKeys'), t3)

        indexTable = new JTable()
        indexModel = new CommonTableModel([resourceBundle.getString('tableMetadata.name'),
                resourceBundle.getString('tableMetadata.fields')] as ArrayList)
        indexModel.valueClass = {col ->
            String.class
        }
        indexModel.valueEditable = {cols, row, col ->
            false
        }
        indexTable.model = indexModel

        def t4 = new JPanel(new BorderLayout(0, 0))
        t4.add(new JScrollPane(indexTable), BorderLayout.CENTER)
        t4.border = BorderFactory.createEmptyBorder(4, 4, 4, 4)
        tabpane.add(resourceBundle.getString('tableMetadata.indexes'), t4)

        // -------------------------------------------------------

        columnScroll = new JScrollPane(columnTable)
        def splitpane = new CleanSplitPane(JSplitPane.VERTICAL_SPLIT, true, columnScroll, tabpane)
        splitpane.resizeWeight = 0.7d

        FormLayout layout = new FormLayout("right:pref, 3dlu, fill:10dlu:grow, 6dlu, right:pref, 3dlu, fill:10dlu:grow", "p, 8dlu, fill:50dlu:grow")

        PanelBuilder builder = new PanelBuilder(layout)
        CellConstraints cc = new CellConstraints()

        builder.addLabel(resourceBundle.getString('tableMetadata.table'), cc.xy(1, 1))
        builder.add(tableComboBox, cc.xy(3, 1))

        builder.addLabel(resourceBundle.getString('tableMetadata.tableType'), cc.xy(5, 1))
        builder.add(typeComboBox, cc.xy(7, 1))

        builder.add(splitpane, cc.xyw(1, 3, 7))

        gui.add(builder.getPanel(), BorderLayout.CENTER)

        // -------------------------------------------------------

        typeComboBox.actionPerformed = {
            def sel = typeComboBox.selectedItem
            if(sel != null && currentType != sel.type) {
                def set = tablesPerType[sel.type]
                if(set != null) {
                    currentType = sel.type
                    tableComboBox.model = new DefaultComboBoxModel(set as Object[])
                    tableComboBox.selectedIndex = -1
                    columnModel.clear()
                    pkModel.clear()
                    fkModel.clear()
                    uniqueModel.clear()
                    indexModel.clear()
                    currentTable = null
                }
            }
        }

        tableComboBox.actionPerformed = {
            def sel = tableComboBox.selectedItem
            if(currentTable != sel) {
                currentTable = sel
                loadTableInfo()
            }
        }

        infoBrowserSelectionChanged = {schemacatalog, type, table, column ->

            if(linkedButton.selected && schemacatalog != null && type != null && table != null) {
                if(schemacatalog == schemaAndCatalog) {
                    if((type != currentType) || (table != currentTable)) {
                        if(type != currentType) {
                            int i = tableTypes.indexOf(type);
                            if(i >= 0) {
                                typeComboBox.selectedIndex = i
                                currentType = typeComboBox.selectedItem.type
                            }
                            else {
                                return
                            }
                        }

                        def set = tablesPerType[currentType]
                        if(set != null) {
                            tableComboBox.model = new DefaultComboBoxModel(set as Object[])
                            columnModel.clear()
                            pkModel.clear()
                            fkModel.clear()
                            uniqueModel.clear()
                            indexModel.clear()

                            int i = set.indexOf(table);
                            if(i >= 0) {
                                currentTable = table
                                tableComboBox.selectedIndex = i
                                loadTableInfo(column)
                            }
                            else {
                                currentTable = null
                                tableComboBox.selectedIndex = -1
                            }
                        }
                    }
                    else if (column != null) {

                        int columnindex = -1
                        for(int i = 0;i < columnModel.getRowCount() && columnindex < 0;i++) {
                            if(column == columnModel.getValueAt(i, 2)) {
                                columnindex = i
                            }
                        }
                        
                        if(columnindex >= 0) {
                            columnTable.setRowSelectionInterval(columnindex, columnindex)
                            columnTable.scrollRectToVisible(columnTable.getCellRect(columnindex, 0, false))
                        }
                    }
                }
                else {
                    schemaAndCatalog = schemacatalog
                    refresh(type, table, column)
                }
            }
        }
        infoBrowser.selectionListeners << infoBrowserSelectionChanged

        if(linkedButton.selected && infoBrowser.schemaAndCatalog) {
            schemaAndCatalog = infoBrowser.schemaAndCatalog
            refresh(infoBrowser.selectedType, infoBrowser.selectedTable, infoBrowser.selectedColumn)
        }
        else {
            refresh()
        }
    }

    // -------------------------------------------------------------------------------

    void dispose() {

        infoBrowser.selectionListeners.remove(infoBrowserSelectionChanged)
    }

    // -------------------------------------------------------------------------------

    void enableFields() {

        refreshButton.enabled = true
        linkedButton.enabled = true
        selectButton.enabled = schemasAndCatalogs.size() > 0
        typeComboBox.enabled = true
        tableComboBox.enabled = true
    }

    void disableFields() {

        refreshButton.enabled = false
        linkedButton.enabled = false
        selectButton.enabled = false
        typeComboBox.enabled = false
        tableComboBox.enabled = false
    }

    // -------------------------------------------------------------------------------

    void loadTableInfo(def selectcolumn = null) {

        if(currentTable == null) {
            return
        }
        
        columnModel.clear()
        pkModel.clear()
        fkModel.clear()
        uniqueModel.clear()
        indexModel.clear()
        disableFields()

        validate()
        repaint()

        startProcessing()

        Thread.start() {

            def columns = []
            def pks = []
            def fks = []
            def uniques = []
            def indexes = []
            int columnindex = -1

            def res = new SqlResources()
            try {
                def keyset = [] as Set

                def con = res << connection.getConnection()

                int i = 0
                def fields = [:]
                def meta = con.getMetaData()
                new SqlResult(res << meta.getColumns(schemaAndCatalog.catalog, schemaAndCatalog.schema, currentTable, null),
                    [new StringField(4), new IntegerField(5, -1), new StringField(6), new IntegerField(7), new IntegerField(9), new StringMatchField(18, ['NO'])]).each() {
                    name, jdbct, type, size, digits, notnull->

                    def field = new TableColumnValue(false,
                        false,
                        name,
                        DatabaseUtils.formatJdbcType(jdbct),
                        type,
                        size,
                        digits,
                        notnull)
                    fields[name] = field
                    columns << field

                    if(name == selectcolumn) {
                        columnindex = i
                    }

                    i++
                }

                // ---------------------------------------------------------------------------

                if('VIEW' != currentType) {
                    def pkname = null
                    def pkfields = new StringBuffer()

                    new SqlResult(res << meta.getPrimaryKeys(schemaAndCatalog.catalog, schemaAndCatalog.schema, currentTable),
                        [new StringField(4), new StringField(6)]).each() {
                        field, pkn ->

                        pkname = pkn
                        if (field != null) {
                            if(fields[field] != null) {
                                fields[field].primaryKey = true
                            }

                            if (pkfields.length() > 0) {
                                pkfields.append(', ')
                            }
                            pkfields.append(field)
                        }
                    }

                    if(pkname != null) {
                        def fnames = pkfields.toString()
                        pks << [pkname, fnames]
                        keyset << pkname
                        keyset << fnames
                    }

                    // ---------------------------------------------------------------------------

                    def fkcache = [:] as TreeMap

                    def rules = [(DatabaseMetaData.importedKeyNoAction): 'NO ACTION',
                        (DatabaseMetaData.importedKeyCascade): 'CASCADE',
                        (DatabaseMetaData.importedKeySetNull): 'SET NULL',
                        (DatabaseMetaData.importedKeySetDefault): 'SET DEFAULT']

                    new SqlResult(res << meta.getImportedKeys(schemaAndCatalog.catalog, schemaAndCatalog.schema, currentTable),
                        [new StringField(3), new StringField(4), new StringField(8), new IntegerTransformField(10, rules), new IntegerTransformField(11, rules), new StringField(12)]).each() {
                        imptable, impcolname, fkcolname, uptrule, delrule, fkname->

                        def foreignkey = fkcache[fkname]
                        if (foreignkey == null) {

                            foreignkey = []
                            fkcache[fkname] = foreignkey

                            foreignkey[0] = fkname
                            foreignkey[1] = ''
                            foreignkey[2] = imptable
                            foreignkey[3] = ''
                            foreignkey[4] = uptrule
                            foreignkey[5] = delrule
                        }

                        if (foreignkey[1].length() > 0) {
                            foreignkey[1] += ', '
                        }
                        foreignkey[1] += fkcolname

                        if(fields[fkcolname] != null) {
                            fields[fkcolname].foreignKey = true
                        }

                        if (foreignkey[3].length() > 0) {
                            foreignkey[3] += ', '
                        }
                        foreignkey[3] += impcolname
                    }

                    fkcache.each() {key, value ->
                        fks << value
                        keyset << value[0]
                        keyset << value[1]
                    }

                    // ---------------------------------------------------------------------------

                    def ixcache = [:] as TreeMap

                    new SqlResult(res << meta.getIndexInfo(schemaAndCatalog.catalog, schemaAndCatalog.schema, currentTable, false, true),
                        [new BooleanField(4), new StringField(6), new StringField(9)]).each() {
                        nonunique, indexname, colname->

                        if (indexname != null && !keyset.contains(indexname)) {

                            def uniquekey = ixcache[indexname]
                            if (uniquekey == null) {

                                uniquekey = []
                                ixcache[indexname] = uniquekey

                                uniquekey[0] = indexname
                                uniquekey[1] = ''

                                if (nonunique) {
                                    indexes << uniquekey
                                }
                                else {
                                    uniques << uniquekey
                                }
                            }

                            if (uniquekey[1].length() > 0) {
                                uniquekey[1] += ', '
                            }
                            uniquekey[1] += colname
                        }
                    }

                    for(Iterator iti = indexes.iterator();iti.hasNext();) {
                        if(keyset.contains(iti.next()[1])) {
                            iti.remove()
                        }
                    }
                    for(Iterator itu = uniques.iterator();itu.hasNext();) {
                        if(keyset.contains(itu.next()[1])) {
                            itu.remove()
                        }
                    }
                }
            }
            catch(Exception exc) {
                Logger.getLogger(getClass().name).log(Level.WARNING, 'Could not receive database meta data!', exc)
            }
            finally {
                res.close()
            }

            SwingUtilities.invokeLater {

                stopProcessing()
                enableFields()

                columnModel.addRows(columns)
                TableUtilities.setOptimalColumnWidths(columnTable)

                pkModel.addRows(pks)
                TableUtilities.setOptimalColumnWidths(pkTable)

                fkModel.addRows(fks)
                TableUtilities.setOptimalColumnWidths(fkTable)

                uniqueModel.addRows(uniques)
                TableUtilities.setOptimalColumnWidths(uniqueTable)

                indexModel.addRows(indexes)
                TableUtilities.setOptimalColumnWidths(indexTable)

                validate()
                repaint()

                if(columnindex >= 0) {
                    columnTable.setRowSelectionInterval(columnindex, columnindex)
                    columnTable.scrollRectToVisible(columnTable.getCellRect(columnindex, 0, false))
                }
            }
        }
    }

    void refresh(def typetoselect = null, def tabletoselect = null, def columntoselect = null) {

        columnModel.clear()
        pkModel.clear()
        fkModel.clear()
        uniqueModel.clear()
        indexModel.clear()
        schemasAndCatalogs.clear()
        tablesPerType.clear()
        tableTypes.clear()
        currentType = null
        currentTable = null
        typeComboBox.model = new DefaultComboBoxModel()
        tableComboBox.model = new DefaultComboBoxModel()

        disableFields()

        validate()
        repaint()

        startProcessing()

        Thread.start() {

            def typesmodel = new DefaultComboBoxModel()
            int seltype = -1

            def res = new SqlResources()
            try {
                def con = res << connection.getConnection()

                def meta = con.getMetaData()
                def schemas = res << meta.getSchemas()
                def resmeta = schemas.getMetaData()
                def full = resmeta.getColumnCount() >= 2
                while (schemas.next()) {
                    def catalog = null
                    def schema = schemas.getString(1)
                    if (full) {
                        catalog = schemas.getString(2)
                    }

                    schemasAndCatalogs << new CatalogSchemaValue(catalog, schema)
                }

                // ------------------------------------

                def tables = res << meta.getTables(schemaAndCatalog.catalog, schemaAndCatalog.schema, null, null)
                while (tables.next()) {
                    def table = tables.getString(3)
                    def type = tables.getString(4)

                    def tableset = tablesPerType[type]
                    if(tableset == null) {
                        tableset = [] as List
                        tablesPerType[type] = tableset
                    }

                    tableset << table
                }
                tables.close()

                int index = 0
                tablesPerType.each() {type, tableset ->

                    def desc = null
                    def nt = DatabaseNode.parseTableType(type)
                    switch(nt) {
                        case DatabaseNode.TYPE_TABLE:
                        desc = resourceBundle.getString('tableMetadata.typeTable')

                        if(seltype < 0) {
                            seltype = index
                        }
                        break

                        case DatabaseNode.TYPE_SYSTEM:
                        desc = resourceBundle.getString('tableMetadata.typeSystem')
                        break

                        case DatabaseNode.TYPE_VIEW:
                        desc = resourceBundle.getString('tableMetadata.typeView')
                        break

                        case DatabaseNode.TYPE_SYNONYM:
                        desc = resourceBundle.getString('tableMetadata.typeSynonym')
                        break

                        default:
                        desc = type
                    }

                    tableTypes << type
                    typesmodel.addElement(new TableTypeValue(type, desc))

                    index++
                }
            }
            catch(Exception exc) {
                Logger.getLogger(getClass().name).log(Level.WARNING, 'Could not receive database meta data!', exc)
            }
            finally {
                res.close()
            }

            SwingUtilities.invokeLater {

                stopProcessing()

                enableFields()

                typeComboBox.model = typesmodel
                if(seltype >= 0) {
                    typeComboBox.selectedIndex = seltype
                }

                def sel = typeComboBox.selectedItem
                if(sel != null) {
                    def set = tablesPerType[sel.type]
                    if(set != null) {
                        currentType = sel.type
                        tableComboBox.model = new DefaultComboBoxModel(set as Object[])
                        tableComboBox.selectedIndex = -1
                    }
                }

                validate()
                repaint()

                if(typetoselect != null && tabletoselect != null) {
                    infoBrowserSelectionChanged(schemaAndCatalog, typetoselect, tabletoselect, columntoselect)
                }
            }
        }
    }

    void selectCatalohAndSchema() {

        if(schemasAndCatalogs != null && schemasAndCatalogs.size() > 0) {

            def popup = new JPopupMenu()

            int count = 0
            def parent = popup
            def blank = uiResources.images['blank16']

            schemasAndCatalogs.each() {val ->

                count++
                if(count > 30) {
                    count = 0
                    JMenu m = new JMenu(resourceBundle.getString('tableMetadata.more'))
                    parent.add(m)
                    parent = m
                }

                def item = new JMenuItem(val.toString(), val == schemaAndCatalog ? uiResources.images['ok16'] : blank)
                parent.add(item)
                item.actionPerformed = {
                    schemaAndCatalog = val
                    refresh()
                }
            }

            popup.show(selectButton, selectButton.width - popup.preferredSize.@width, selectButton.height)
        }
    }
}
